﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using System.Data;

namespace Runda.CMS.Dal
{
    public partial class SysUserDal
    {
        public void UpdatePassword(Guid UserID, string Password)
        {
            string sql = "update Cms_Sys_User set Password=@Password where UserID = @UserID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@Password", System.Data.DbType.String, Password);
            CurrentDatabase.AddInParameter(dbcommand, "@UserID", System.Data.DbType.Guid, UserID);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        //更新数据（sql）
        public int Executesql(string sql)
        {
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public DataTable GetUsersByDeptID(Guid DepartmentID)
        {
            string sql = @"select a.*,c.DepartmentName from Cms_Sys_User a 
join Cms_Sys_UserDepartment b on a.userid = b.userid
join Cms_Sys_Department c on b.DepartmentID = c.DepartmentID
where 1=1 {0} order by a.Username asc";

            if (DepartmentID != Guid.Empty)
            {
                sql = string.Format(sql, "and b.DepartmentID = @DepartmentID");
            }
            else
            {
                sql = string.Format(sql, "");
            }

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            if (DepartmentID != Guid.Empty)
            {
                CurrentDatabase.AddInParameter(dbcommand, "@DepartmentID", DbType.Guid, DepartmentID);
            }
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable GetUsersByRole(int roletype)
        {
            string sql = @"select a.userid,a.username from Cms_Sys_User a 
join Cms_Sys_UserRoleRelation b on a.userid = b.userid
join Cms_Sys_Dictionary c on b.RoleID = c.Code
where 1=1 {0} order by a.Username asc";

            sql = string.Format(sql, "and c.Type = @RoleType");
           

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            CurrentDatabase.AddInParameter(dbcommand, "@RoleType", DbType.Int16, roletype);
            
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable GetUserList(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from cms_sys_user where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

    }
}
